package org.abacus.jdbc.util;

import java.util.Iterator;
import java.util.List;

import org.dom4j.Attribute;
import org.dom4j.Document;
import org.dom4j.DocumentException;
import org.dom4j.Element;
import org.dom4j.io.SAXReader;
import org.dom4j.tree.DefaultElement;

public class SqlFactoryFromXML {
	String xmlFileName;
	static String path;
	Element element;
	static {
		path = SqlFactoryFromXML.class.getResource("/").getPath();
	}

	public SqlFactoryFromXML(String xmlFileName) {
		super();
		this.xmlFileName = xmlFileName;
		SAXReader saxReader = new SAXReader();
		Document document = null;
		try {
			document = saxReader.read(path + "/" + this.xmlFileName);
		} catch (DocumentException e) {
			e.printStackTrace();
		}
		element = document.getRootElement();
	}

	DefaultElement de;

	public String getSql(Long id) {
		StringBuffer sb = new StringBuffer();
		Iterator iterator = element.elementIterator();
		Object o;
		while (iterator.hasNext()) {
			o = iterator.next();
			de = (DefaultElement) o;
			if ("select".equals(de.getName())) {
				Attribute attr = de.attribute("id");
				if ((id + "").equals(attr.getValue())) {
					sb.append("select");
					sb.append(' ');
					append_column(de, sb);
					int index = sb.lastIndexOf(",");
					if (-1 != index) {
						sb.deleteCharAt(index);
					}
					append_from(de, sb);
					boolean flag = append_where(de, sb);
					if (flag) {
						index = sb.lastIndexOf(" and ");
						sb.delete(index, index + 4);
					}
					flag = append_groupby(de, sb);
					if (flag) {
						index = sb.lastIndexOf(",");
						sb.deleteCharAt(index);
					}
					flag = append_orderby(de, sb);
					if (flag) {
						index = sb.lastIndexOf(",");
						sb.deleteCharAt(index);
					}
				}
			}
		}
		return sb.toString();
	}

	private boolean append_groupby(DefaultElement de2, StringBuffer sb) {
		boolean a = true;
		List wlist = de2.selectNodes("group-by");
		Iterator witerator = wlist.iterator();
		DefaultElement wdefaultElement = null;
		while (witerator.hasNext()) {
			wdefaultElement = (DefaultElement) witerator.next();
			List list = wdefaultElement.selectNodes("column");
			Iterator iterator = list.iterator();
			DefaultElement defaultElement = null;
			Attribute attr = null;
			while (iterator.hasNext()) {
				defaultElement = (DefaultElement) iterator.next();
				attr = defaultElement.attribute("name");
				if (null != attr && null != attr.getValue() && !"".equals(attr.getValue().trim())) {
					if (a) {
						sb.append("group by");
						sb.append(' ');
						a = false;
					}
					sb.append(attr.getValue().trim());
				}
				sb.append(", ");
			}
		}
		return !a;
	}

	private boolean append_orderby(DefaultElement de2, StringBuffer sb) {
		boolean a = true;
		List wlist = de2.selectNodes("order-by");
		Iterator witerator = wlist.iterator();
		DefaultElement wdefaultElement = null;
		while (witerator.hasNext()) {
			wdefaultElement = (DefaultElement) witerator.next();
			List list = wdefaultElement.selectNodes("column");
			Iterator iterator = list.iterator();
			DefaultElement defaultElement = null;
			Attribute attr = null;
			while (iterator.hasNext()) {
				defaultElement = (DefaultElement) iterator.next();
				attr = defaultElement.attribute("name");
				if (null != attr && null != attr.getValue() && !"".equals(attr.getValue().trim())) {
					if (a) {
						sb.append("order by");
						sb.append(' ');
						a = false;
					}
					sb.append(attr.getValue().trim());
				}
				attr = defaultElement.attribute("sorted");
				if (null != attr && null != attr.getValue() && !"".equals(attr.getValue().trim())) {
					sb.append(attr.getValue().trim());
				} else {
					sb.append(' ');
					sb.append("asc");
				}
				sb.append(", ");
			}
		}
		return !a;
	}

	private boolean append_where(DefaultElement de2, StringBuffer sb) {
		boolean a = true;
		List wlist = de2.selectNodes("where");
		Iterator witerator = wlist.iterator();
		DefaultElement wdefaultElement = null;
		while (witerator.hasNext()) {
			wdefaultElement = (DefaultElement) witerator.next();
			List list = wdefaultElement.selectNodes("column");
			Iterator iterator = list.iterator();
			DefaultElement defaultElement = null;
			Attribute attr = null;
			while (iterator.hasNext()) {
				defaultElement = (DefaultElement) iterator.next();
				attr = defaultElement.attribute("name");
				if (null != attr && null != attr.getValue() && !"".equals(attr.getValue().trim())) {
					if (a) {
						sb.append("where");
						sb.append(' ');
						a = false;
					}
					sb.append(attr.getValue().trim());
				}
				attr = defaultElement.attribute("criterion");
				if (null != attr && null != attr.getValue() && !"".equals(attr.getValue().trim())) {
					if ("gt".equals(attr.getValue().trim())) {
						sb.append(">");
					} else if ("lt".equals(attr.getValue().trim())) {
						sb.append(">");
					} else if ("ne".equals(attr.getValue().trim())) {
						sb.append("<>");
					} else {
						sb.append(attr.getValue().trim());
					}
				}
				attr = defaultElement.attribute("value");
				if (null != attr && null != attr.getValue() && !"".equals(attr.getValue().trim())) {
					sb.append(attr.getValue().trim());
				}
				sb.append(" and ");
			}
		}
		return !a;
	}

	private void append_from(DefaultElement de2, StringBuffer sb) {
		boolean a = true;
		List list = de2.selectNodes("from");
		Iterator iterator = list.iterator();
		DefaultElement defaultElement = null;
		Attribute attr = null;
		while (iterator.hasNext()) {
			defaultElement = (DefaultElement) iterator.next();
			attr = defaultElement.attribute("table");
			if (null != attr && null != attr.getValue() && !"".equals(attr.getValue().trim())) {
				if (a) {
					sb.append("from");
					sb.append(' ');
					a = false;
				}
				sb.append(attr.getValue().trim());
				sb.append(' ');
			}
			sb.append(' ');
		}
	}

	private void append_column(DefaultElement de, StringBuffer sb) {
		List list = de.selectNodes("column");
		Iterator iterator = list.iterator();
		DefaultElement defaultElement = null;
		Attribute attr = null;
		while (iterator.hasNext()) {
			defaultElement = (DefaultElement) iterator.next();
			attr = defaultElement.attribute("name");
			if (null != attr && null != attr.getValue() && !"".equals(attr.getValue().trim())) {
				sb.append(attr.getValue().trim());
				sb.append(' ');
			}
			attr = defaultElement.attribute("lable");
			if (null != attr && null != attr.getValue() && !"".equals(attr.getValue().trim())) {
				sb.append("as");
				sb.append(' ');
				sb.append(attr.getValue().trim());
			}
			sb.append(',');
			sb.append(' ');
		}
	}
}
